package com.fanyue.modules.wzb.mapper;

import com.fanyue.modules.wzb.entity.ClothingInfoEntity;
import com.fanyue.modules.wzb.vo.CountClothingByCategoryVO;
import com.fanyue.modules.wzb.vo.CountClothingBySpecVO;
import com.fanyue.modules.wzb.vo.CountLendByAppUserVO;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 人员信息
 */
public interface ClothingInfoMapper extends BaseMapper<ClothingInfoEntity> {

    /*@Select("<script>" +
            "SELECT count(1)\n" +
            "FROM (\n" +
            "\tSELECT op_user_id\n" +
            "\tFROM wzb_clothing_info clothing\n" +
            "\tLEFT JOIN wzb_app_user user on user.id = clothing.op_user_id\n" +
            "\tWHERE clothing.status = 2\n" +
            "<if test='param.category != null'>\n" +
            "\t\tand clothing.category = #{param.category}\n" +
            "</if>\n" +
            "<if test=\"param.keyWord != null and param.keyWord != ''\">\n" +
            "\t\tand (\n" +
            "\t\t\tuser.name like concat('%',#{param.keyWord},'%') \n" +
            "\t\t\tor user.uid like concat('%',#{param.keyWord},'%') \n" +
            "\t\t)\n" +
            "</if>\n" +
            "\tGROUP BY clothing.op_user_id\n" +
            ") a" +
            "</script>")
    Integer lendByAppUserTotal(@Param("param") Map<String,Object> param);*/

    @Select("<script>" +
            "SELECT user.name username,user.uid userUid," +
            "opUser.num,dept.name departmentName,\n" +
            "\tcategory1.num as categoryNum1,\n" +
            "\tcategory2.num as categoryNum2,\n" +
            "\tcategory3.num as categoryNum3,\n" +
            "\tcategory4.num as categoryNum4\n" +
            "FROM \n" +
            "\t(\n" +
            "\t\tSELECT op_user_id,count(1) num\n" +
            "\t\tFROM wzb_clothing_info\n" +
            "\t\tWHERE status = 2\n" +
            "<if test='param.category != null'>\n" +
            "\t\tand category = #{param.category}\n" +
            "</if>\n" +
            "\t\tGROUP BY op_user_id\n" +
            "\t) opUser\n" +
            "LEFT JOIN wzb_app_user user on user.id = opUser.op_user_id\n" +
            "LEFT JOIN base_sys_department dept on dept.id = user.department_id\n" +
            "LEFT JOIN\n" +
            "\t(\n" +
            "\t\tSELECT op_user_id,count(1) num\n" +
            "\t\tFROM wzb_clothing_info\n" +
            "\t\tWHERE status = 2 and category = 1\n" +
            "\t\tGROUP BY op_user_id\n" +
            "\t) category1 on category1.op_user_id = opUser.op_user_id\n" +
            "LEFT JOIN\n" +
            "\t(\n" +
            "\t\tSELECT op_user_id,count(1) num\n" +
            "\t\tFROM wzb_clothing_info\n" +
            "\t\tWHERE status = 2 and category = 2\n" +
            "\t\tGROUP BY op_user_id\n" +
            "\t) category2 on category2.op_user_id = opUser.op_user_id\n" +
            "LEFT JOIN\n" +
            "\t(\n" +
            "\t\tSELECT op_user_id,count(1) num\n" +
            "\t\tFROM wzb_clothing_info\n" +
            "\t\tWHERE status = 2 and category = 3\n" +
            "\t\tGROUP BY op_user_id\n" +
            "\t) category3 on category3.op_user_id = opUser.op_user_id\n" +
            "LEFT JOIN\n" +
            "\t(\n" +
            "\t\tSELECT op_user_id,count(1) num\n" +
            "\t\tFROM wzb_clothing_info\n" +
            "\t\tWHERE status = 2 and category = 4\n" +
            "\t\tGROUP BY op_user_id\n" +
            "\t) category4 on category4.op_user_id = opUser.op_user_id\n" +
            "WHERE 1=1 " +
            "<if test=\"param.keyWord != null and param.keyWord != ''\">\n" +
            "\t\tand (\n" +
            "\t\t\tuser.name like concat('%',#{param.keyWord},'%') \n" +
            "\t\t\tor user.uid like concat('%',#{param.keyWord},'%') \n" +
            "\t\t)\n" +
            "</if>\n" +
            "<if test=\"param.department == 'true'\">\n" +
            "\t\tand user.department_id in " +
            "<foreach item='id' index='index' collection='departmentIds' open='(' separator=',' close=')'>" +
            "#{id} " +
            "</foreach>" +
            "</if>\n" +
            "ORDER BY " +
            "<if test='param.category == 1'>\n" +
            "\t\tcategoryNum1 desc,\n" +
            "</if>\n" +
            "<if test='param.category == 2'>\n" +
            "\t\tcategoryNum2 desc,\n" +
            "</if>\n" +
            "<if test='param.category == 3'>\n" +
            "\t\tcategoryNum3 desc,\n" +
            "</if>\n" +
            "<if test='param.category == 4'>\n" +
            "\t\tcategoryNum4 desc,\n" +
            "</if>\n" +
            "num desc \n" +
            /*"LIMIT #{param.page_size} OFFSET #{param.page_number}" +*/
            "</script>")
    List<CountLendByAppUserVO> lendByAppUserList(@Param("param") Map<String, Object> selectMap, @Param("departmentIds") List<Long> departmentIds);

    @Select("<script>" +
            "SELECT \n" +
            "\tc.season,c.category,c.spec_id,\n" +
            "\ts.name specName,count(1) total,\n" +
            "\tsum(if(c.status = 0, 1, 0)) num0,\n" +
            "\tsum(if(c.status = 1, 1, 0)) num1,\n" +
            "\tsum(if(c.status = 2, 1, 0)) num2,\n" +
            "\tsum(if(c.status = 3, 1, 0)) num3,\n" +
            "\tsum(if(c.status = 4, 1, 0)) num4,\n" +
            "\tsum(if(c.status = 9, 1, 0)) num9\n" +
            "FROM wzb_clothing_info c\n" +
            "LEFT JOIN wzb_clothing_spec s on s.id = c.spec_id\n" +
            "WHERE 1 = 1\n" +
            "<if test='param.season != null'>\n" +
            "\t\tand c.season = #{param.season}\n" +
            "</if>\n" +
            "<if test='param.category != null'>\n" +
            "\t\tand c.category = #{param.category}\n" +
            "</if>\n" +
            "<if test='param.specId != null'>\n" +
            "\t\tand c.spec_id = #{param.specId}\n" +
            "</if>\n" +
            "GROUP BY c.season,c.category,c.spec_id " +
            "order by total desc" +
            "</script>")
    List<CountClothingBySpecVO> clothingBySpec(@Param("param") Map<String, Object> selectMap);

    @Select("<script>" +
            "SELECT \n" +
            "\tc.category,count(1) total,\n" +
            "\tsum(if(c.status = 0, 1, 0)) num0,\n" +
            "\tsum(if(c.status = 1, 1, 0)) num1,\n" +
            "\tsum(if(c.status = 2, 1, 0)) num2,\n" +
            "\tsum(if(c.status = 3, 1, 0)) num3,\n" +
            "\tsum(if(c.status = 4, 1, 0)) num4,\n" +
            "\tsum(if(c.status = 9, 1, 0)) num9\n" +
            "FROM wzb_clothing_info c\n" +
            "GROUP BY c.category\n" +
            "</script>")
    List<CountClothingByCategoryVO> clothingByCategory();
}
